package com.mayinghao.stat.tongji;

import java.text.SimpleDateFormat;
import java.util.Date;



public class LnProductSql {
	  public String getNetworkSql(String begintime, String endtime, int flag,String section)
	    {  //section="0";
	        String sql = "select substring(lastLoginDate,1,8)as lastLoginDate,count(*)as zongshu from users where domainName='m10060.com' and username like '"+section+"%' and state=1";
	       // String sql = "select substring(lastLoginDate,1,8)as lastLoginDate,count(*)as zongshu from users where domainName='m10060.com'";
	        if(begintime != "")
	            sql = sql + " and substring(lastLoginDate,1,8)>='" + begintime + "'";
	        if(endtime != "")
	            sql = sql + " and substring(lastLoginDate,1,8)<='" + endtime + "'";
	       // System.out.println("sql="+sql);
	        switch(flag)
	        {
	        case 0: // '\0'
	            if(begintime == "" && endtime == "")
	                sql = sql + " and substring(lastLoginDate,1,8)>='20060501'";
	            break;

	        case 1: // '\001'
	            sql = sql + " and grade=1 ";
	            break;

	        case 2: // '\002'
	            sql = sql + " and grade=2 ";
	            break;

	        case 3: // '\003'
	            sql = sql + " and grade=3 ";
	            break;

	        default:
	            sql = "sql_network_error";
	            break;
	        }
	        return sql+"group by substring(lastLoginDate,1,8)";
	    }
	  public String getDateTemp(String begintime, String endtime, int flag,String section)
	    {  //section="0";
	        String sql = "select substring(createdDate,1,8)as createdDate,count(*)as zongshu from users where domainName='m10060.com' and username like '"+section+"%' and state=1";
	       // String sql = "select substring(lastLoginDate,1,8)as lastLoginDate,count(*)as zongshu from users where domainName='m10060.com'";
	        if(begintime != "")
	            sql = sql + " and substring(createdDate,1,8)>='" + begintime + "'";
	        if(endtime != "")
	            sql = sql + " and substring(createdDate,1,8)<='" + endtime + "'";
	       // System.out.println("sql="+sql);
	        switch(flag)
	        {
	        case 0: // '\0'
	            if(begintime == "" && endtime == "")
	                sql = sql + " and substring(createdDate,1,8)>='20060501'";
	            break;

	        case 1: // '\001'
	            sql = sql + " and grade=1 ";
	            break;

	        case 2: // '\002'
	            sql = sql + " and grade=2 ";
	            break;

	        case 3: // '\003'
	            sql = sql + " and grade=3 ";
	            break;

	        default:
	            sql = "sql_network_error";
	            break;
	        }
	        return sql+"group by substring(createdDate,1,8)";
	    }
	  public String getSmsUp(String begintime, String endtime,String section)
	    {
	        String sql = "select count(distinct destno)as smsnum,substring(smstime,1,8)as smstime from mz_sms_mo_log where substring(mobileno,1,8)like '19116%' and destno not like '024%' and destno not like '041%' and destno not like '042%' and destno like '"+section+"%'";
	        if(begintime != "")
	            sql = sql + " and substring(smstime,1,8)>='" + begintime + "'";
	        if(endtime != "")
	            sql = sql + " and substring(smstime,1,8)<='" + endtime + "'";
	        sql = sql + " group by substring(smstime,1,8)";
	        return sql;
	    }
	    public String getAllMsg(String begintime, String endtime, int flag,String section)
	    {
	        String sql_users = "select substring(lastLoginDate,1,8)as lastLoginDate,count(*)as tongji from users where domainName='m10060.com' and username like '"+section+"%'";
	        String sql = "select substring(register_time,1,10)as register_time,count(distinct(register_code)) as tongji from register_kind where register_code not like'043%' and register_code not like '044%' and register_code like '"+section+"%'";
	        String sql_login = "select substring(visit_time,1,10)as visit_time, count(*)as tongji from login_log where page='lnindex'";
	        if(begintime != "")
	        {
	            sql = sql + " and substring(register_time,1,8)>='" + begintime.substring(0, 4) + "-" + begintime.substring(4, 6) + "-" + begintime.substring(6, 8) + "'";
	            sql_users = sql_users + " and substring(lastLoginDate,1,8)>='" + begintime + "'";
	            sql_login = sql_login + " and substring(visit_time,1,10)>= '" + begintime.substring(0, 4) + "-" + begintime.substring(4, 6) + "-" + begintime.substring(6, 8) + "'";
	        }
	        if(endtime != "")
	        {
	            sql = sql + " and substring(register_time,1,10)<='" + endtime.substring(0, 4) + "-" + endtime.substring(4, 6) + "-" + endtime.substring(6, 8) + "'";
	            sql_users = sql_users + "and substring(lastLoginDate,1,8)<='" + endtime + "'";
	            sql_login = sql_login + " and substring(visit_time,1,10)<= '" + endtime.substring(0, 4) + "-" + endtime.substring(4, 6) + "-" + endtime.substring(6, 8) + "'";
	        }
	        switch(flag)
	        {
	        case 4: // '\004'
	            sql = sql + " and register_type=0  group by substring(register_time,1,10)";
	            break;

	        case 5: // '\005'
	            sql = sql + " and register_type=1  group by substring(register_time,1,10)";
	            break;

	        case 6: // '\006'
	            sql_users = sql_users + " and pwd !='123456'";
	            sql = sql_users;
	            break;

	        case 7: // '\007'
	            sql_users = sql_users + " and state=7  group by substring(lastLoginDate,1,8)";
	            sql = sql_users;
	            break;

	        case 8: // '\b'
	            sql_users = sql_users + " and state=0  group by substring(lastLoginDate,1,8)";
	            sql = sql_users;
	            break;

	        case 9: // '\t'
	            sql = sql_login+"  group by substring(visit_time,1,10)";
	            break;

	        case 10: // '\n'
	            sql_users = sql_users + " and ppwd!='123456'";
	            sql = sql_users;
	            break;

	        default:
	            sql = "sql_all_error";
	            break;
	        }
	        return sql;
	    }
	    public String getMailsql(String begintime, String endtime, int flag,String section)
	    {
	        String sql = "";
	        String sql_ftp = "SELECT count(*) as num_sql FROM `userftpexts`where username like '"+section+"%' and username not like '04318850450' and username not like '04318131551'and curSize>0.0 and domainName='m10060.com'";
	        String sql_to = "SELECT sum(mailNum)as num_sql FROM `smtplog`where email like '%@m10060.com' and email not like '04318850450%' and email not like '04318131551%' and email like '"+section+"%'";
	        String sql_from = "SELECT sum(mailNum)as num_sql FROM `smtplog1`where email like '%@m10060.com' and email not like '04318850450%' and email not like '04318131551%' and email like '"+section+"%'";
	        if(begintime != "")
	        {
	            int strDate = Integer.parseInt(begintime);
	            Date dt_start = null;
	            try
	            {
	                dt_start = (new SimpleDateFormat("yyyyMMddhhmmss")).parse(strDate + "000000");
	            }
	            catch(Exception exception) { }
	            sql_ftp = sql_ftp + " and createdDate>='00" + dt_start.getTime() + "'";
	            sql_to = sql_to + " and logDate>='00" + dt_start.getTime() + "'";
	            sql_from = sql_from + " and logDate>='00" + dt_start.getTime() + "'";
	        }
	        if(endtime != "")
	        {
	            int strDate = Integer.parseInt(endtime)+1;
	            Date dt_start = null;
	            try
	            {
	                dt_start = (new SimpleDateFormat("yyyyMMddhhmmss")).parse(strDate + "000000");
	            }
	            catch(Exception exception1) { }
	            sql_ftp = sql_ftp + " and createdDate<'00" + dt_start.getTime() + "'";
	            sql_to = sql_to + " and logDate<'00" + dt_start.getTime() + "'";
	            sql_from = sql_from + " and logDate<'00" + dt_start.getTime() + "'";
	        }
	        switch(flag)
	        {
	        case 11: // '\013'
	            sql = sql_ftp;
	            break;

	        case 12: // '\f'
	            sql = sql_to;
	            break;

	        case 13: // '\r'
	            sql = sql_from;
	            break;

	        default:
	            sql = "error_mysql";
	            break;
	        }
	        return sql;
	    }
	public static void main(String[] args) {
		System.out.println(new ProductSql().getNetworkSql("20060821","20060821",1,"024"));

	}

}
